﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;


namespace DataModel
{
    public class daProduct
    {
        string myConn = System.Configuration.ConfigurationManager.ConnectionStrings["DataConnection"].ConnectionString;

        public dsProduct GetProductList()
        {
            using (SqlConnection cn = new SqlConnection(myConn))
            {
                SqlDataAdapter da = new SqlDataAdapter("select id, catid,scatid,productname,productsource,productdesc,productimage from tblproduct", cn);

                SqlCommandBuilder scb = new SqlCommandBuilder(da);
                dsProduct product = new dsProduct();


                int result = da.Fill(product, "tblproduct");
                da.Dispose();
                scb.Dispose();
                return product;
            }
        }

        public dsProduct GetProductBy(string id)
        {
            int _id = -1;
            int.TryParse(id, out _id);
            return GetProductBy(_id);
        }

        public dsProduct GetProductBy(int id)
        {
            using (SqlConnection cn = new SqlConnection(myConn))
            {
                SqlDataAdapter da = new SqlDataAdapter("select id, catid,scatid,productname,productsource,productdesc,productimage from tblproduct where id =" + id.ToString(), cn);

                SqlCommandBuilder scb = new SqlCommandBuilder(da);
                dsProduct product = new dsProduct();


                int result = da.Fill(product, "tblproduct");
                da.Dispose();
                scb.Dispose();
                return product;
            }
        }

        public bool UpdateProduct(dsProduct ds)
        {
            bool _result = false;
            try
            {
                using (SqlConnection cn = new SqlConnection(myConn))
                {
                    SqlDataAdapter da = new SqlDataAdapter(@"select id, 
                    catid,scatid,productname,productsource,productdesc,productimage from tblproduct", cn);

                    SqlCommandBuilder scb = new SqlCommandBuilder(da);

                    int result = da.Update(ds.Tables[0]);
                    ds.AcceptChanges();

                    da.Dispose();
                    scb.Dispose();
                    _result = true;
                }
            }
            catch (Exception exp)
            {
                _result = false;
            }

            return _result;
        }

        public bool DeleteProduct(string id)
        {
            using (SqlConnection cn = new SqlConnection(myConn))
            {
                string sql = "delete from tblproduct where id =" + id;

                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                int irows = cmd.ExecuteNonQuery();
                return irows > 0;
            }
        }

        public dsProduct GetAllCat()
        {
            using (SqlConnection cn = new SqlConnection(myConn))
            {
                dsProduct cat = new dsProduct();
                dsProductTableAdapters.catproductTableAdapter catadapter = new dsProductTableAdapters.catproductTableAdapter();
                catadapter.Connection = cn;
                catadapter.Fill(cat.catproduct);

                catadapter.Dispose();

                return cat;
            }
        }
    }
}
